﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Collections;
using System.Data;

namespace DAO
{
    public class SachDAO
    {
        public static ArrayList LayDanhSach()
        {
            ArrayList arr = new ArrayList();
            string sql;
            sql = "Select * From Sach WHERE TrangThai=1";
            DataTable dt = DataAccess.ExcuQuery(sql);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                SachDTO dv = new SachDTO();
                dv.MaSach = Convert.ToString(dt.Rows[i]["MaSach"]);
                dv.TheLoai = Convert.ToString(dt.Rows[i]["TheLoai"]);
                dv.TacGia = Convert.ToString(dt.Rows[i]["TacGia"]);
                dv.DonGiaBan = float.Parse(dt.Rows[i]["GiaBan"].ToString());
                dv.SoLuongTon = Convert.ToInt32(dt.Rows[i]["SoLuongTon"]);
                dv.TrangThai = Convert.ToInt32(dt.Rows[i]["TrangThai"]);
                dv.TenSach = Convert.ToString(dt.Rows[i]["TenSach"]);
                arr.Add(dv);
            }
            return arr;
        }


       
        public static SachDTO LayThongTinSach(String maSach)
        {
            SachDTO sach = new SachDTO();
            string sql = "Select * From Sach Where MaSach = '"+ maSach + "'";
            DataTable dt = DataAccess.ExcuQuery(sql);

            if (dt != null && dt.Rows.Count > 0)
            {
                sach.MaSach = Convert.ToString(dt.Rows[0]["MaSach"]);
                sach.TheLoai = Convert.ToString(dt.Rows[0]["TheLoai"]);
                sach.TacGia = Convert.ToString(dt.Rows[0]["TacGia"]);
                sach.DonGiaBan = float.Parse(dt.Rows[0]["GiaBan"].ToString());
                sach.SoLuongTon = Convert.ToInt32(dt.Rows[0]["SoLuongTon"]);
                sach.TrangThai = Convert.ToInt32(dt.Rows[0]["TrangThai"]);
                sach.TenSach = Convert.ToString(dt.Rows[0]["TenSach"]);
            }
            return sach;
        }

        public static ArrayList TimKiem(Hashtable tieuchi)
        {
            ArrayList arr = new ArrayList();
            string sql;
            sql = "Select * From Sach where TrangThai=1";

            if (tieuchi.Contains("MaSach"))
                sql += " and MaSach= '" + tieuchi["MaSach"] + "'";
            if (tieuchi.Contains("TenSach"))
                sql += " and TenSach like N'%" + tieuchi["TenSach"] + "%'";
            if (tieuchi.Contains("TheLoai"))
                sql += " and TheLoai =N'%" + tieuchi["TheLoai"] + "%'";
            if (tieuchi.Contains("TacGia"))
                sql += " and TacGia like N'%" + tieuchi["TacGia"] + "%'";
            if (tieuchi.Contains("GiaTu"))
                sql += " and DonGia >=" + tieuchi["GiaTu"] + "";
            if (tieuchi.Contains("GiaDen"))
                sql += " and DonGia <=" + tieuchi["GiaDen"] + "";
            if (tieuchi.Contains("TonTu"))
                sql += " and SoLuongTon >=" + tieuchi["TonTu"] + "";
            if (tieuchi.Contains("TonDen"))
                sql += " and SoLuongTon <=" + tieuchi["TonDen"] + "";

            DataTable dt = DataAccess.ExcuQuery(sql);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                SachDTO dv = new SachDTO();
                dv.MaSach = Convert.ToString(dt.Rows[i]["MaSach"]);
                dv.TheLoai = Convert.ToString(dt.Rows[i]["TheLoai"]);
                dv.TacGia = Convert.ToString(dt.Rows[i]["TacGia"]);
                dv.DonGiaBan = float.Parse(dt.Rows[i]["GiaBan"].ToString());
                dv.SoLuongTon = Convert.ToInt32(dt.Rows[i]["SoLuongTon"]);
                dv.TrangThai = Convert.ToInt32(dt.Rows[i]["TrangThai"]);
                dv.TenSach = Convert.ToString(dt.Rows[i]["TenSach"]);
                arr.Add(dv);
            }
            return arr;
        }


        public static int CapNhatSoLuongTon(String masach, int soluong)
        {
            string sql;
            sql = "Update Sach Set SoLuongTon = " + soluong + " where MaSach = '" + masach + "'";
            int kq = DataAccess.ExcuNonQuery(sql);
            return kq;
        }
        public static int ThemSach(SachDTO sach)
        {
            string sql;
            sql = "Insert into Sach(MaSach,TheLoai,TacGia,SoLuongTon,GiaBan,TrangThai,TenSach) values (";
            sql += " '" + sach.MaSach + "',";
            sql += " '" + sach.TheLoai + "',";
            sql += " '" + sach.TacGia + "',";
            sql += "" + sach.SoLuongTon + ",";
            sql += "" + sach.DonGiaBan + ",";
            sql += "" + 1 + ",";
            sql += " '" + sach.TenSach + "')";
            int kq = DataAccess.ExcuNonQuery(sql);
            return kq;
        }
        public static int XoaSach(SachDTO sach)
        {
            string sql;
            sql = "Update Sach Set TrangThai= " + 0 + "";
            sql += " Where MaSach = '" + sach.MaSach + "'";
            int kq = DataAccess.ExcuNonQuery(sql);
            return kq;
        }
        public static int CapNhatSach(SachDTO sach)
        {
            string sql;
            sql = "Update Sach Set TheLoai= '" + sach.TheLoai + "',";
            sql += "TacGia= '" + sach.TacGia + "',";
            sql += "SoLuongTon=" + sach.SoLuongTon + ",";
            sql += "DonGia=" + sach.DonGiaBan + ",";
            sql += "TenSach= '" + sach.TenSach + "'";
            sql += " Where MaSach ='," + sach.MaSach + "' ";
            int kq = DataAccess.ExcuNonQuery(sql);
            return kq;
        }
        
    }
}
